# coding=utf-8
import functools

from MySQLdb import connect


class DbConnection(object):
    def __init__(self):
        self.connection = None
        self.transaction = 0

    def is_init(self):
        return self.connection is not None

    def init(self):
        self.connection = connect(
            host='localhost',
            user='root',
            passwd='1364140672',
            db='shanb',
            charset='utf8'
        )
        self.transaction = 0

    def cursor(self):
        return self.connection.cursor()

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    def clean_up(self):
        self.connection.close()
        self.connection = None


db_connection = DbConnection()


class Db(object):
    def __enter__(self):
        self.should_cleanup = False
        if not db_connection.is_init():
            db_connection.init()
            self.should_cleanup = True
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.should_cleanup:
            db_connection.clean_up()


def with_connection(func):
    @functools.wraps(func)
    def _wrapper(*args, **kwargs):
        with Db():
            return func(*args, **kwargs)

    return _wrapper


class DbTransaction(object):
    def __enter__(self):
        print('__enter__')
        self.should_cleanup = False
        if not db_connection.is_init():
            db_connection.init()
            self.should_cleanup = True
        db_connection.transaction += 1
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        print('__exit__')
        db_connection.transaction -= 1
        try:
            if db_connection.transaction == 0:
                if exc_type is None:
                    # 没有出现异常，数据库提交
                    self.commit()
                else:
                    self.rollback()
        finally:
            if self.should_cleanup:
                db_connection.clean_up()

    @classmethod
    def commit(cls):
        try:
            db_connection.commit()
        except:
            db_connection.rollback()
            raise BaseException()

    @classmethod
    def rollback(cls):
        db_connection.rollback()


def with_transaction(func):
    @functools.wraps(func)
    def _wrapper(*args, **kwargs):
        with DbTransaction():
            return func(*args, **kwargs)

    return _wrapper


class DbModel(object):
    @staticmethod
    @with_connection
    def _select(sql, first, *args):
        cursor = None
        sql = sql.replace('?', '%s')
        print(sql % args)
        try:
            cursor = db_connection.cursor()
            cursor.execute(sql, args)
            values = cursor.fetchall()
            if first:
                values = values[0:1]
            return values
        finally:
            if cursor:
                cursor.close()

    @staticmethod
    @with_connection
    def _save(sql, *args):
        cursor = None
        sql = sql.replace('?', '%s')
        print(sql % args)
        try:
            cursor = db_connection.cursor()
            cursor.execute(sql, args)
            rowcount = cursor.rowcount
            last_row_id = cursor.lastrowid
            if db_connection.transaction == 0:
                db_connection.commit()
            return last_row_id, rowcount
        finally:
            if cursor:
                cursor.close()

    @classmethod
    def find(cls, sql, *args):
        return cls._select(sql, True, *args)

    @classmethod
    def select_one(cls, table, cols, *args):
        cols = ' and '.join(['`%s`=?' % col for col in cols])
        sql = 'select * from `%s` where %s' % (table, cols)
        result = cls._select(sql, True, *args)
        if result:
            return result[0]
        return None

    @classmethod
    def select(cls, table, cols, *args):
        cols = ' and '.join(['`%s`=?' % col for col in cols])
        sql = 'select * from `%s` where %s' % (table, cols)
        result = cls._select(sql, False, *args)
        if result:
            return result
        return None

    @classmethod
    def save(cls, table, cols, *args):
        values = ', '.join(['?' for col in cols])
        cols = ', '.join(['`%s`' % col for col in cols])
        sql = 'insert into `%s`(%s) values(%s)' % (table, cols, values)
        return cls._save(sql, *args)

    @classmethod
    @with_transaction
    def save_profile(cls, *args):
        table, cols, values = args[0]
        word = cls.save(table, cols, *values)

        table, cols, values = args[1]
        values[0], _ = word
        list = cls.save(table, cols, *values)

        table, cols, values = args[2]
        values[0], _ = word
        example = cls.save(table, cols, *values)

        return word, example

if __name__ == '__main__':
    word_id = DbModel.select_one('word_level', ['level'], '四级')
    print word_id
